package model

import (
	"database/sql"
	"encoding/json"
	"time"
	"fmt"
	"math/rand"
	"strconv"
	"strings"
	"errors"
	"crypto/md5"
	"encoding/hex"
	"io"
)

type (
	PromotionOfficer struct {
		Id              int    `json:"id"`
		Phone           string `json:"phone"`
		Password        string `json:"password"`
		Master          int    `json:"master"`
		Apprentices     string `json:"apprentices"`
		Withdrawable    int    `json:"withdrawable"`
		Income          int    `json:"income"`
		CreatedDate     int    `json:"created_date"`
		UpdatedDate		int    `json:"updated_date"`
		Status          int    `json:"status"`
	}
	PromotionOfficerInfo struct {
		Id              string `json:"id"`
		Phone           string `json:"phone"`
		Withdrawable    int    `json:"withdrawable"`
		Income          int    `json:"income"`
	}
	Order struct {
		Id                          	int		`json:"id"`
        Phone                       	string  `json:"phone"`
		TradingAt                  		string 	`json:"trading_at"`
     	Status                      	int     `json:"status"`
		ProductId                  		int     `json:"product_id"`
		ProductType                		int     `json:"product_type"`
		ProductName                	  	string 	`json:"product_name"`
		PromotionOfficerId 	  			int     `json:"promotion_officer_id"`
		Income                          int     `json:"income"`  
		PromotionOfficerIncome 		    int     `json:"promotion_officer_income"`
		MasterIncome                    int     `json:"master_income"`
		PlatformIncome                  int     `json:"platform_income"`
		PromotionOfficerWithdrawable	int     `json:"promotion_officer_withdrawable"`
		CreatedDate 					int     `json:"created_date"`
		UpdatedDate 					int     `json:"updated_date"`
	}
    AssociateInfo struct {
		Withdrawable 	int 		`json:"withdrawable"`
		Income          int   		`json:"income"`
	}
	Apprentice struct {
		Id              int			`json:"id"`
        Phone           string		`json:"phone"`
		CreatedDate 	int     	`json:"created_date"`
	}
	
)

func generatorMD5(code string) string {
	MD5 := md5.New()
	_, _ = io.WriteString(MD5, code)
	return hex.EncodeToString(MD5.Sum(nil))
}

func redisOrderList() (ret []*Order, err error) {
	err = rdb.Del(ctx, "order_list").Err()
	if err != nil {
		return ret, err
	}

	rows, err := db.Query("SELECT * FROM order_list ORDER BY id")
	if err != nil {
		return ret, err
	}

	defer rows.Close()

	for rows.Next() {
		o := Order{}
		if err := rows.Scan(&o.Id, &o.Phone, &o.TradingAt, &o.Status, &o.ProductId, &o.ProductType, &o.ProductName, &o.PromotionOfficerId, &o.Income, &o.PromotionOfficerIncome, &o.MasterIncome, &o.PlatformIncome, &o.PromotionOfficerWithdrawable, &o.CreatedDate, &o.UpdatedDate); err != nil {
			return ret, err
		}

		ret = append(ret, &o)

		jsonStu, err := json.Marshal(o)
		if err != nil {
			return ret, err
		}

		err = rdb.RPush(ctx, "order_list", jsonStu).Err()
		if err != nil {
			return ret, err
		}
	}

	return ret, nil
}

func redisPromotionOfficer() (ret []*PromotionOfficer, err error) {
	err = rdb.Del(ctx, "promotion_officer").Err()
	if err != nil {
		return ret, err
	}

	rows, err := db.Query("SELECT * FROM promotion_officer ORDER BY updated_date DESC")
	if err != nil {
		return ret, err
	}

	for rows.Next() {
		p := PromotionOfficer{}
		if err := rows.Scan(&p.Id, &p.Phone, &p.Password, &p.Master, &p.Apprentices, &p.Withdrawable, &p.Income, &p.CreatedDate, &p.UpdatedDate, &p.Status); err != nil {
			return ret, err
		}

		ret = append(ret, &p)

		jsonStu, err := json.Marshal(p)
		if err != nil {
			return ret, err
		}

		err = rdb.RPush(ctx, "promotion_officer", jsonStu).Err()
		if err != nil {
			return ret, err
		}
	}

	defer rows.Close()

	return ret, nil
}

func (p *PromotionOfficer) GetPromotionOfficer() (ret []*PromotionOfficer, err error) {
	vals, err := rdb.LRange(ctx, "promotion_officer", 0, -1).Result()
	if err != nil {
		return nil, err
	}

	if len(vals) > 0 {
		for _, v := range vals {
			p := PromotionOfficer{}
			err := json.Unmarshal([]byte(v), &p)
			if err != nil {
				return ret, err
			}

			ret = append(ret, &p)
		}

		return ret, nil
	}

	ret, err = redisPromotionOfficer()
	if err != nil {
		return ret, err
	}

	return ret, nil
}

func Makepwd(pwl int) string {
	chars := "1234567890"
	clen := float64(len(chars))
	ret := ""

	rand.Seed(time.Now().Unix())

	for i := 0; i < pwl; i++ {
	  rfi := int(clen * rand.Float64())
	  ret += fmt.Sprintf("%c", chars[rfi])
	}

	return ret
}

func (p *PromotionOfficer) CreatePromotionOfficer(phone string) (pwd string, err error) {
	tx, err := db.Begin()
	if err != nil {
		tx.Rollback()
		return pwd, err
	}

	stmt, err := tx.Prepare("SELECT id FROM promotion_officer WHERE phone=?")
	if err != nil {
		tx.Rollback()
		return pwd, err
	}

	var id int
	err = stmt.QueryRow(phone).Scan(&id)
	if err != nil && err != sql.ErrNoRows {
		tx.Rollback()
		return pwd, err
	}

	if err == sql.ErrNoRows {
		pwd = Makepwd(6)
		pwdMD5 := generatorMD5(pwd)

		stmt1, err := tx.Prepare("INSERT promotion_officer SET phone=?, password=?, master=?, apprentices=?, withdrawable=?, income=?, created_date=?, updated_date=?, status=?")
		if err != nil {
			tx.Rollback()
			return pwd, err
		}

		_, err = stmt1.Exec(phone, pwdMD5, 0, "", 0, 0, time.Now().Unix(), time.Now().Unix(), 1)
		if err != nil {
			tx.Rollback()
			return pwd, err
		}

		stmt2, err := tx.Prepare("SELECT id FROM promotion_officer WHERE phone=?")
		if err != nil {
			tx.Rollback()
			return pwd, err
		}

		var pid int
		err = stmt2.QueryRow(phone).Scan(&pid)
		if err != nil {
			tx.Rollback()
			return pwd, err
		}

		stmt2x, err := tx.Prepare("SELECT id FROM customer WHERE phone=?")
		if err != nil {
			tx.Rollback()
			return pwd, err
		}

		var cid int
		err = stmt2x.QueryRow(phone).Scan(&cid)
		if err != nil && err != sql.ErrNoRows {
			tx.Rollback()
			return pwd, err
		}

		if err == sql.ErrNoRows {
			stmt3, err := tx.Prepare("INSERT customer SET phone=?, created_date=?, updated_date=?, is_promotion=?, promotion_id=?, source_user_id=?")
			if err != nil {
				tx.Rollback()
				return pwd, err
			}

			_, err = stmt3.Exec(phone, time.Now().Unix(), time.Now().Unix(), 1, pid, 0)
			if err != nil {
				tx.Rollback()
				return pwd, err
			}
		} else {
			stmt3, err := tx.Prepare("UPDATE customer SET phone=?, updated_date=?, is_promotion=?, promotion_id=?, source_user_id=? WHERE id=?")
			if err != nil {
				tx.Rollback()
				return pwd, err
			}

			_, err = stmt3.Exec(phone, time.Now().Unix(), 1, pid, 0, cid)
			if err != nil {
				tx.Rollback()
				return pwd, err
			}
		}

		tx.Commit()

		_, err = redisPromotionOfficer()
		if err != nil {
			return pwd, err
		}

		return pwd, nil
	}

	tx.Commit()

	return pwd, errors.New("手机号码已存在")
}

func (p *PromotionOfficer) SynPromotionOfficerAndOrder(associateInfoMap map[int]*PromotionOfficer, newOrders []*Order) error {
	sql1 := "UPDATE promotion_officer SET withdrawable = CASE id "

	var sqlMid1, sqlMid2, sqlEnd string

	idStrings := []string{}
	for k, _ := range associateInfoMap {
		idStrings = append(idStrings, strconv.Itoa(k))
	}

	for i := 0; i < len(idStrings); i++ {
		if i == len(idStrings) -1 {
			sqlEnd += idStrings[i]
		} else {
			sqlEnd += idStrings[i] + ","
		}
	}

	for k, v := range associateInfoMap {
		idStr := strconv.Itoa(k)
		withdrawableStr := strconv.Itoa(v.Withdrawable)
		income := strconv.Itoa(v.Income)

		sqlMid1 += "WHEN " + idStr + " THEN " + withdrawableStr + " "
		sqlMid2 += "WHEN " + idStr + " THEN " + income + " "
	}

	sql1 += sqlMid1
	sql1 += "END, "
	sql1 += "income = CASE id "
	sql1 += sqlMid2
	sql1 += "END "
	sql1 += "WHERE id IN (" + sqlEnd + ")"

	tx, err := db.Begin()

	stmt, err := tx.Prepare(sql1)
	if err != nil {
		tx.Rollback()
		return err
	}

	_, err = stmt.Exec()
	if err != nil {
		tx.Rollback()
		return err
	}

	sql2 := "INSERT INTO order_list (phone,trading_at,status,product_id,product_type,product_name,promotion_officer_id,income,promotion_officer_income,master_income,platform_income,promotion_officer_withdrawable,created_date,updated_date)\n"
	sql2 += "VALUES\n"

	for k, v := range newOrders {
		sql2 += "('" + v.Phone + "','" + v.TradingAt + "'," + strconv.Itoa(v.Status) + "," + strconv.Itoa(v.ProductId) + "," + strconv.Itoa(v.ProductType) + ",'" + v.ProductName + "'," + strconv.Itoa(v.PromotionOfficerId) + "," + strconv.Itoa(v.Income) + "," + strconv.Itoa(v.PromotionOfficerIncome) + "," + strconv.Itoa(v.MasterIncome) + ","  + strconv.Itoa(v.PlatformIncome) + "," + strconv.Itoa(v.PromotionOfficerWithdrawable) + "," + strconv.FormatInt(time.Now().Unix(), 10) + "," + strconv.FormatInt(time.Now().Unix(), 10) + ")"

		if k !=  len(newOrders) - 1 {
			sql2 += ",\n"
		} else {
			sql2 += "\n"
		}
	}

	stmt1, err := tx.Prepare(sql2)
	if err != nil {
		tx.Rollback()
		return err
	}

	_, err = stmt1.Exec()
	if err != nil {
		tx.Rollback()
		return err
	}

	tx.Commit()

	_, err = redisPromotionOfficer()
	if err != nil {
		tx.Rollback()
		return err
	}

	_, err = redisOrderList()
	if err != nil {
		tx.Rollback()
		return err
	}

	return nil
}

func (p *PromotionOfficer) DeletePromotionOfficer(status int, id int) error {
	stmt, err := db.Prepare("UPDATE promotion_officer SET status=? WHERE id=?")
	if err != nil {
		return err
	}

	_, err = stmt.Exec(status, id)
	if err != nil {
		return err
	}

	_, err = redisPromotionOfficer()
	if err != nil {
		return err
	}

	return nil
}

func (p *PromotionOfficerInfo) GetPromotionOfficerInfo(cid int, phone string) (ret PromotionOfficerInfo, err error) {
	stmt, err := db.Prepare("SELECT promotion_id FROM customer WHERE id=?")
	if err != nil {
		return ret, err
	}

	var pid int
	err = stmt.QueryRow(cid).Scan(&pid)
	if err != nil {
		return ret, err
	}

	stmt1, err := db.Prepare("SELECT id, phone, withdrawable, income, status FROM promotion_officer WHERE id=?")
	if err != nil {
		return ret, err
	}

	var status int

	err = stmt1.QueryRow(pid).Scan(&ret.Id, &ret.Phone, &ret.Withdrawable, &ret.Income, &status)
	if err != nil {
		return ret, err
	}

	if status == 0 {
		return ret, errors.New("因违规操作，您的账号暂时被锁定")
	}

	if phone != ret.Phone {
		return ret, errors.New("Token信息错误")
	}

	return ret, nil
}

func (p *PromotionOfficer) GetApprentices(phone string) (ret []*Apprentice, num int, err error) {
	stmt, err := db.Prepare("SELECT apprentices, status FROM promotion_officer WHERE phone=?")
	if err != nil {
		return ret, num, err
	}

	var apprentices string
	var status int
	err = stmt.QueryRow(phone).Scan(&apprentices, &status)
	if err != nil {
		return ret, num, err
	}

	if status == 0 {
		return ret, num, errors.New("因违规操作，您的账号暂时被锁定")
	}

	if len(apprentices) > 0 {
		s := strings.Split(apprentices, ",")

		sql1 := "SELECT id, phone, created_date FROM promotion_officer WHERE "

		for k, v := range s {
			if k == len(s) - 1 {
				sql1 += "id=" + v + ";"
			} else {
				sql1 += "id=" + v + " OR "
			}
		}

		rows, err := db.Query(sql1)
		if err != nil {
			return ret, num, err
		}

		defer rows.Close()

		ts := time.Now().AddDate(0, 0, -1)
		timeYesterday := time.Date(ts.Year(), ts.Month(), ts.Day(), 0, 0, 0, 0, ts.Location())
		stampYesterday := timeYesterday.Unix()

		ts = time.Now().AddDate(0, 0, 0)
		timeToday := time.Date(ts.Year(), ts.Month(), ts.Day(), 0, 0, 0, 0, ts.Location())
		stampTody := timeToday.Unix()

		for rows.Next() {
			a := Apprentice{}
			if err := rows.Scan(&a.Id, &a.Phone, &a.CreatedDate); err != nil {
				return ret, num, err
			}
			ret = append(ret, &a)

			if a.CreatedDate > int(stampYesterday) && a.CreatedDate < int(stampTody) {
				num ++
			}
		}

		return ret, num, err
	}

	return ret, num, err
}

func (p *PromotionOfficer) GetPromotionApprentices(id int) (ret []*PromotionOfficer, err error) {
	stmt, err := db.Prepare("SELECT apprentices FROM promotion_officer WHERE id=?")
	if err != nil {
		return ret, err
	}

	var apprentices string
	err = stmt.QueryRow(id).Scan(&apprentices)
	if err != nil {
		return ret, err
	}

	if len(apprentices) > 0 {
		s := strings.Split(apprentices, ",")

		sql1 := "SELECT id, phone, withdrawable, income, created_date, updated_date FROM promotion_officer WHERE "

		for k, v := range s {
			if k == len(s) - 1 {
				sql1 += "id=" + v + ";"
			} else {
				sql1 += "id=" + v + " OR "
			}
		}

		rows, err := db.Query(sql1)
		if err != nil {
			return ret, err
		}

		defer rows.Close()

		for rows.Next() {
			p := PromotionOfficer{}
			if err := rows.Scan(&p.Id, &p.Phone, &p.Withdrawable, &p.Income, &p.CreatedDate, &p.UpdatedDate); err != nil {
				return ret, err
			}
			ret = append(ret, &p)
		}
	}

	return ret, err
}

func (p *PromotionOfficer) DeletePromotionApprentices(id int, aid int) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	stmt, err := tx.Prepare("SELECT apprentices FROM promotion_officer WHERE id=?")
	if err != nil {
		tx.Rollback()
		return err
	}

	var apprentices string
	err = stmt.QueryRow(id).Scan(&apprentices)
	if err != nil {
		tx.Rollback()
		return err
	}

	if len(apprentices) > 0 {
		s := strings.Split(apprentices, ",")

		for k, v := range s {
			vInt, err := strconv.Atoi(v)

			if err != nil {
				return err
			}

			if vInt == aid {
				s = append(s[:k], s[k+1:]...)
			}
		}

		sString := strings.Replace(strings.Trim(fmt.Sprint(s), "[]"), " ", ",", -1)

		stmt1, err := tx.Prepare("UPDATE promotion_officer SET apprentices=?, updated_date=? WHERE id=?")
		if err != nil {
			tx.Rollback()
			return err
		}

		_, err = stmt1.Exec(sString, time.Now().Unix(), id)
		if err != nil {
			tx.Rollback()
			return err
		}

		stmt2, err := tx.Prepare("UPDATE promotion_officer SET master=?, updated_date=? WHERE id=?")
		if err != nil {
			tx.Rollback()
			return err
		}

		_, err = stmt2.Exec(0, time.Now().Unix(), aid)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	tx.Commit()

	_, err = redisPromotionOfficer()
	if err != nil {
		tx.Rollback()
		return err
	}

	_, err = redisCustomer()
	if err != nil {
		tx.Rollback()
		return err
	}

	return nil
}